package com.open.capacity.finance.util;


import com.open.capacity.finance.dto.SlmxzDto;
import com.open.capacity.finance.dto.ZbBookDto;
import com.open.capacity.finance.dto.ZbIndexDto;
import com.open.capacity.finance.dto.ZbTitleDto;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.List;

public class ExcelZzzb {
    private HSSFWorkbook workbook;
    private HSSFSheet sheet;
    private HSSFSheet sheetIndex;

    /**
     * 创建行元素
     *
     * @param style  样式
     * @param height 行高
     * @param value  行显示的内容
     * @param row1   起始行
     * @param row2   结束行
     * @param col1   起始列
     * @param col2   结束列
     */
    private void createRow(HSSFCellStyle style, int height, String value, int row1, int row2, int col1, int col2) {

        sheet.addMergedRegion(new CellRangeAddress(row1, row2, col1, col2));  //设置从第row1行合并到第row2行，第col1列合并到col2列
        HSSFRow rows = sheet.createRow(row1);        //设置第几行
        rows.setHeight((short) height);              //设置行高
        HSSFCell cell = rows.createCell(col1);       //设置内容开始的列
        cell.setCellStyle(style);                    //设置样式
        cell.setCellValue(value);                    //设置该行的值
    }

    /**
     * 创建样式
     *
     * @param fontSize 字体大小
     * @param align    水平位置  左右居中2 居右3 默认居左 垂直均为居中
     * @param bold     是否加粗
     * @return
     */
    private HSSFCellStyle getStyle(int fontSize, HorizontalAlignment align, boolean bold, boolean border) {
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) fontSize);// 字体大小
        if (bold) {
            font.setBold(true);
        }
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFont(font);                         //设置字体
        style.setAlignment(align);          // 左右居中2 居右3 默认居左
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中1
        if (border) {
            style.setBorderRight(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setLocked(true);
        }
        return style;
    }

    /**
     * 根据数据集生成Excel，并返回Excel文件流
     *
     * @param data      数据集
     * @param sheetName Excel中sheet单元名称
     * @param headNames 列表头名称数组
     * @return
     * @throws IOException
     */
    public InputStream getExcelFile(ZbBookDto data, String sheetName, String[] headNames,
                                    int colWidths[]) throws IOException {
        workbook = new HSSFWorkbook();
        sheetIndex = workbook.createSheet("目录");
        sheet = workbook.createSheet(sheetName);
        int startRow = 0;
        int page = 1;

        // 目录
        HSSFRow row = sheetIndex.createRow(0);
        sheetIndex.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
        row.setHeight((short) 0x500);
        HSSFCell cell1_1 = row.createCell(0);
        cell1_1.setCellStyle(getStyle(22, HorizontalAlignment.CENTER, false, false));
        cell1_1.setCellValue("目录");

        HSSFCellStyle cellStyle = getStyle(11, HorizontalAlignment.CENTER, false, true);
        setIndexCellData(data.index, cellStyle);

        HSSFCellStyle cellStyle_center = getStyle(22, HorizontalAlignment.CENTER, true, false);//样式
        HSSFCellStyle cellStyle_left = getStyle(11, HorizontalAlignment.LEFT, false, false);
        HSSFCellStyle cellStyle_right = getStyle(11, HorizontalAlignment.RIGHT, false, false);
        HSSFCellStyle cellStyle2 = getStyle(11, HorizontalAlignment.RIGHT, false, true);
        //主表
        for (ZbTitleDto dto : data.content) {
            // 创建表头 startRow代表表体开始的行
            startRow = createHeadCell(dto, cellStyle_center, cellStyle_left, cellStyle_right, page++, headNames, colWidths, startRow);
            // 创建表体数据
            setCellData(dto.pzflList, cellStyle, cellStyle2, startRow);

//        //创建表尾
//        createTailCell(data.size() + 4, headNames.length);

            startRow += 28;
        }

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        byte[] ba = baos.toByteArray();
        ByteArrayInputStream bais = new ByteArrayInputStream(ba);
        return bais;
    }


    /**
     * 创建表头
     *
     * @param headNames
     * @param colWidths
     */
    private int createHeadCell(ZbTitleDto dto, HSSFCellStyle stylecenter, HSSFCellStyle styleleft, HSSFCellStyle styleright, int page, String[] headNames, int colWidths[], int startRow) {
        // 表头标题
        HSSFCellStyle titleStyle = stylecenter;//样式
        createRow(titleStyle, 0x500, "总分类账", startRow, startRow, 0, headNames.length - 1);
        //第二行
        HSSFRow row = sheet.createRow(startRow + 1);
        sheet.addMergedRegion(new CellRangeAddress(startRow + 1, startRow + 1, 0, 1));
        row.setHeight((short) 0x190);
        HSSFCell cell1_1 = row.createCell(0);
        cell1_1.setCellStyle(getStyle(11, HorizontalAlignment.LEFT, false, false));
        cell1_1.setCellValue("科目:" + dto.getKjkmName());

        //第二行右边部分
        HSSFCell cell1_3 = row.createCell(5);
        cell1_3.setCellStyle(getStyle(11, HorizontalAlignment.RIGHT, false, false));
        cell1_3.setCellValue("第" + page + "页");

        //第三行左边部分
        sheet.addMergedRegion(new CellRangeAddress(startRow + 2, startRow + 2, 0, 1));
        HSSFRow row3 = sheet.createRow(startRow + 2);
        row3.setHeight((short) 0x190);
        HSSFCell cell = row3.createCell(0);
        cell.setCellStyle(styleleft);
        cell.setCellValue("账套:" + dto.getZtName());

        //第三行中间部分
        HSSFCell cell2 = row3.createCell(2);
        cell2.setCellStyle(styleleft);
        cell2.setCellValue(dto.getKjnd());

        //第三行右边部分
        HSSFCell cell3 = row3.createCell(5);
        cell3.setCellStyle(styleright);
        cell3.setCellValue("单位: 元");

        //第四行表头
        boolean b = (headNames != null && headNames.length > 0);
        if (b) {
            HSSFRow row2 = sheet.createRow(startRow + 3);
            row2.setHeight((short) 0x170);
            HSSFCell fcell = null;

            HSSFCellStyle cellStyle = getStyle(11, HorizontalAlignment.CENTER, false, true);

            for (int i = 0; i < headNames.length; i++) {
                fcell = row2.createCell(i);

                fcell.setCellStyle(cellStyle);
                fcell.setCellValue(headNames[i]);
                if (colWidths != null && i < colWidths.length) {
                    sheet.setColumnWidth(i, 32 * colWidths[i]);
                }
            }
        }
        return b ? startRow + 4 : startRow + 3;  //从哪一行开始渲染表体
    }

    /**
     * 创建表体数据
     *
     * @param data      表体数据
     * @param cellStyle 样式
     */
    private void setIndexCellData(List<ZbIndexDto> data, HSSFCellStyle cellStyle) {
        // 创建数据
        HSSFRow row = null;
        HSSFCell cell = null;
        int i = 2;

        sheetIndex.setColumnWidth(0, 32 * 700);
        sheetIndex.setColumnWidth(1, 32 * 100);

        if (data != null && data.size() > 0) {
            row = sheetIndex.createRow(1);
            row.setHeight((short) 0x170);
            cell = row.createCell(0);
            cell.setCellStyle(getStyle(11, HorizontalAlignment.LEFT, false, true));
            cell.setCellType(CellType.STRING);
            cell.setCellValue("科目信息");

            cell = row.createCell(1);
            cell.setCellStyle(cellStyle);
            cell.setCellType(CellType.STRING);
            cell.setCellValue("页码");

            for (ZbIndexDto rowData : data) {
                row = sheetIndex.createRow(i);
                row.setHeight((short) 0x170);
                cell = row.createCell(0);
                cell.setCellStyle(getStyle(11, HorizontalAlignment.LEFT, false, true));
                cell.setCellType(CellType.STRING);
                cell.setCellValue(rowData.name);

                cell = row.createCell(1);
                cell.setCellStyle(cellStyle);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(rowData.page);

                i++;
            }
        }
    }

    /**
     * 创建表体数据
     *
     * @param data      表体数据
     * @param cellStyle 样式
     * @param startRow  开始行
     */
    private void setCellData(List<SlmxzDto> data, HSSFCellStyle cellStyle, HSSFCellStyle cellStyle2, int startRow) {
        // 创建数据
        HSSFRow row = null;
        HSSFCell cell = null;
        int i = startRow;

        if (data != null && data.size() > 0) {
            DecimalFormat df = new DecimalFormat("##,##0.00");
            for (SlmxzDto rowData : data) {
                row = sheet.createRow(i);
                row.setHeight((short) 0x170);
                cell = row.createCell(0);
                cell.setCellStyle(cellStyle);
                cell.setCellType(CellType.STRING);
                String kjqj = "";
                if (rowData.getKjqj() > 0 && rowData.getKjqj() < 10) {
                    kjqj = "0" + rowData.getKjqj();
                } else {
                    kjqj = "" + rowData.getKjqj();
                }
                cell.setCellValue(kjqj);

                cell = row.createCell(1);
                cell.setCellStyle(cellStyle);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(rowData.getZy());

                cell = row.createCell(2);
                cell.setCellStyle(cellStyle2);
                cell.setCellType(CellType.NUMERIC);
                String j = df.format(rowData.getJ());
                cell.setCellValue(j);

                cell = row.createCell(3);
                cell.setCellStyle(cellStyle2);
                cell.setCellType(CellType.NUMERIC);
                String d = df.format(rowData.getD());
                cell.setCellValue(d);

                cell = row.createCell(4);
                cell.setCellStyle(cellStyle);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(rowData.getYefxStr());


                cell = row.createCell(5);
                cell.setCellStyle(cellStyle2);
                cell.setCellType(CellType.NUMERIC);
                if (rowData.getYe() == null) {
                    rowData.setYe(BigDecimal.ZERO);
                }
                String ye = df.format(rowData.getYe());
                cell.setCellValue(ye);
                i++;
            }
        }
    }

//    /**
//     * 创建表尾
//     *
//     * @param size
//     * @param length
//     */
//    private void createTailCell(int size, int length) {
//        HSSFCellStyle remarkStyle1 = getStyle(11, 1, false, false);
//        createRow(remarkStyle1, 0x190, "经核对，确认以上数据真实无误。", size, size, 0, length - 2);
//
//        HSSFCellStyle remarkStyle2 = getStyle(10, 1, false, false);
//        createRow(remarkStyle2, 0x160, "(联系人：XXX；联系电话：13xxxxxxxx；邮箱:123456789@qq.com)", size + 1, size + 1, 0, length - 2);
//
//        HSSFRow row3 = sheet.createRow(size + 2);
//        row3.setHeight((short) 0x379);
//
//        sheet.addMergedRegion(new CellRangeAddress(size + 3, size + 3, 0, 1));
//        HSSFRow row4 = sheet.createRow(size + 3);
//        row4.setHeight((short) 0x190);
//        HSSFCell cell4 = row4.createCell(0);
//        cell4.setCellStyle(getStyle(11, 1, false, false));
//        cell4.setCellValue("单位核对人：");
//
//        sheet.addMergedRegion(new CellRangeAddress(size + 3, size + 3, 2, 4));
//        HSSFCell cell15 = row4.createCell(2);
//        cell15.setCellStyle(getStyle(11, 1, false, false));
//        cell15.setCellValue("单位制表人：");
//
//        HSSFCellStyle dateStyle = getStyle(10, 3, false, false);
//        createRow(dateStyle, 0x150, "公司公章                     ", size + 8, size + 8, 0, length - 2);
//
//        createRow(dateStyle, 0x150, year + "年  月   日", size + 9, size + 9, 0, length - 2);
//
//    }

}
